package com.yunhe.sms.dao;

import com.yunhe.sms.mode.Student;
import com.yunhe.sms.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    /**
     * 获得学生列表
     * @return
     */
    public static List<Student> getAllStudent(){
    Connection conn = JdbcUtil.getConn();
    String sql = "select *from student order by no asc ";
    PreparedStatement stmt = JdbcUtil.getstmt(conn, sql);
    ResultSet resultSet = JdbcUtil.sqlQuery(stmt);
    ArrayList<Student> studentList = new ArrayList<>();
    Student student =null;
    if (null!=resultSet){
        try {
            while (resultSet.next()){
                int no = resultSet.getInt("no");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                String age = resultSet.getString("age");
                String phone = resultSet.getString("phone");
                student =new Student(no,name,sex,age,phone);
                studentList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    JdbcUtil.close(conn,stmt,resultSet);
    return studentList;
    }

    /**
     * 添加学员
     * @param student
     */

    public static void studentAdd(Student student){
        if (null!=student){
            Connection conn = JdbcUtil.getConn();
            String sql ="insert into student(no,name,sex,age,phone) values(?,?,?,?,?)  ";
            PreparedStatement stmt =JdbcUtil.getstmt(conn, sql, student.getId(), student.getName(), student.getSex(), student.getAge(), student.getPhone());
            JdbcUtil.sqlUpdate(stmt);
            JdbcUtil.close(stmt);
            JdbcUtil.close(conn);
        }
    }
    /**
     * 修改学员信息
     * @param student
     */
    public static void studentUpdate(Student student){
        if (null!=student){
            Connection conn = JdbcUtil.getConn();
            String sql ="update student set name =?, sex =?, age =?, phone =? where no =?  ";
            PreparedStatement stmt= JdbcUtil.getstmt(conn, sql, student.getName(), student.getSex(), student.getAge(), student.getPhone(), student.getId());
            JdbcUtil.sqlUpdate(stmt);
            JdbcUtil.close(stmt);
            JdbcUtil.close(conn);
        }
    }

    /**
     * 删除学员
     * @param no
     */
    public static void studentDle(int no){
        Connection conn = JdbcUtil.getConn();
        String sql =" delete from student where no =? ";
        PreparedStatement stmt = JdbcUtil.getstmt(conn, sql, no);
        JdbcUtil.sqlUpdate(stmt);
        JdbcUtil.close(stmt);
        JdbcUtil.close(conn);
    }
}
